package com.efesco.stat.finance;

import java.util.Date;
import java.util.List;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.DBStatement;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.utils.variant.VariantSet;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * monthReport_oughtReceiveDatasetListener
 */
public class monthReport_oughtReceiveDatasetListener extends AbstractDatasetListener {

  public void afterLoadData(Dataset dataset)
     throws Exception {
		DoradoContext context = DoradoContext.getContext();
	    String sql="",foreign_enterprise_id="",fesc_user_id="",sql1="",sql2="";

	    Object obj = context.getAttribute(context.SESSION, "User");
	    if (obj != null) {
	    	User user = (User) obj;
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		fesc_user_id=user.getForEmployeeId();
    		String startMonth="",endMonth="",singleCompanyId="";

    		startMonth = Tools.toStr(dataset.parameters().getString("startMonth"));
    		endMonth = Tools.toStr(dataset.parameters().getString("endMonth"));
    		singleCompanyId = Tools.toStr(dataset.parameters().getString("singleCompanyId"));
    		
	    	sql1="select cmp.single_company_id,cmp.company_code,cmp.company_chinese_name,o.ought_month,ptt.type_class,nvl(sum(pd.amount),0) amount " +
	    			"from s_single_company cmp,s_ought_receive o,s_payment_detail pd,s_product pt,s_product_type ptt,s_company_businessman cbm " +
	    			"where o.payment_version_id=pd.payment_version_id and pd.product_id=pt.product_id and cmp.single_company_id=cbm.single_company_id " +
	    			"and pt.product_type_id=ptt.product_type_id and cmp.single_company_id=o.single_company_id " +
	    			"and cmp.foreign_enterprise_id="+foreign_enterprise_id+
    				" and exists " + DBTools.getRightSqlByBusinessman(fesc_user_id,"cbm") ;
	    	sql2="select i.single_company_id,h.ought_month,nvl(sum(h.gathering_amount),0) gathering_amount " +
	    			"from s_invoice i,s_hr_invoice_detail h " +
	    			"where i.invoice_id=h.invoice_id and i.if_effect=1 "; 

    		if (!startMonth.equals(""))  {
    			sql1 = sql1 + " and o.ought_month >= '"+startMonth+"' ";
    			sql2 = sql2 + " and h.ought_month >= '"+startMonth+"' ";
    		}
    		if (!endMonth.equals(""))  {
    			sql1 = sql1 + " and o.ought_month <= '"+endMonth+"' ";
    			sql2 = sql2 + " and h.ought_month <= '"+startMonth+"' ";
    		}
    		if (!singleCompanyId.equals(""))  {
    			sql1 = sql1 + " and cmp.single_company_id = "+singleCompanyId;
    			sql2 = sql2 + " and i.single_company_id = "+singleCompanyId;
    		}
	
    		sql1 = sql1 + " group by cmp.single_company_id,cmp.single_company_id,o.single_company_id,cmp.company_code,cmp.company_chinese_name,o.ought_month,ptt.type_class " ;
    		sql2 = sql2 + " group by i.single_company_id,h.ought_month " ;
    		
    		sql = "select a.*,b.gathering_amount from ("+sql1+") a,("+sql2+") b where a.single_company_id=b.single_company_id and a.ought_month=b.ought_month " ;
	
    		DBStatement statement = new DBStatement();
    		statement.setSql(sql);
    		List list=statement.queryForList();
    		
    		String tag="";

    		for (int j=0;j<list.size();j++)  {
    			VariantSet var=(VariantSet)list.get(j);
    			if (tag.equals(var.getString("company_code")+var.getString("ought_month"))) {
    				dataset.setString(var.getString("type_class"),var.getString("amount"));
    			} 
    			else {
    				dataset.insertRecord();
    				tag = var.getString("company_code")+var.getString("ought_month");
    				dataset.setString("company_code", var.getString("company_code"));
    				dataset.setString("company_chinese_name", var.getString("company_chinese_name"));
    				dataset.setString("ought_month", var.getString("ought_month"));
    				dataset.setString(var.getString("type_class"),var.getString("amount"));
    				dataset.setString("gathering_amount",var.getString("gathering_amount"));
    			}
    		}
    		Tools.toSumDataset(dataset,"company_chinese_name");
    		statement.close();
	    }
  }

}
